﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Web.Caching;
using System.Configuration;
using System.Data.SqlClient;
using System.ComponentModel;

public class Stock
{
    public string Name;
    public double Price;
    public string Date;
    public double Open;
    public double High;
    public double Low;
    public double Close;
    public string Movement;
}

public class Game
{
    public string StartDate;
    public string EndDate;
}

public class Transaction
{
    public int TransactionID;
    public string BuyDate;
    public int BuyQuantity;
    public double BuyValue;
    public double BuyTotal;
    public string SellDate;
    public int SellQuantity;
    public double SellValue;
    public double SellTotal;
}


/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 

[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    private SqlConnection mySqlConnection;
    private SqlCommand mySqlCommand;
    private SqlDataAdapter mySqlDataAdapter;
    private DataSet myDataSet;

    public WebService()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    
    [WebMethod]
    public List<Stock> displayStockOnDefault(String GameID, String UserName)
    {
        List<Stock> plotGraphList = new List<Stock>();
        int num;
        if (Int32.TryParse(GameID, out num))
        {
            mySqlConnection = new SqlConnection(GetConnectionString());
            DataSet dataTable = null;

            dataTable = this.LoadData(GameID, UserName);

            foreach (DataRow dr in dataTable.Tables["data"].Rows)
            {

                Stock stock = new Stock();

                stock.Name = dr["ShareSymbol"].ToString();

                stock.Open = Convert.ToDouble(dr["CloseValue"].ToString());

                stock.Movement = dr["Movement"].ToString();

                plotGraphList.Add(stock);

            }
        }

        return plotGraphList;
    }

    private DataSet LoadData(string gameID, string userName)
    {
        int num;
        if (Int32.TryParse(gameID, out num))
        {
            try
            {
                mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "GetUserRelatedStockValue";
                mySqlCommand.CommandType = CommandType.StoredProcedure;
                mySqlCommand.Parameters.AddWithValue("@GameID", gameID);
                mySqlCommand.Parameters.AddWithValue("@UserName", userName);

                mySqlDataAdapter = new SqlDataAdapter();
                mySqlDataAdapter.SelectCommand = mySqlCommand;
                myDataSet = new DataSet();

                OpenConnection();

                mySqlDataAdapter.Fill(myDataSet, "data");

                CloseConnection();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex2)
            {
                throw ex2;
            }
        }

        return myDataSet;
    }

    [WebMethod]
    public List<Stock> DisplayStockHistory(string shareSymbol, string gameID, string startDate, string endDate)
    {
        List<Stock> plotGraphList = new List<Stock>();
        int num;
        if (Int32.TryParse(gameID, out num))
        {
            mySqlConnection = new SqlConnection(GetConnectionString());
            DataSet dataTable = null;

            dataTable = this.LoadData(shareSymbol, gameID, startDate, endDate);

            foreach (DataRow dr in dataTable.Tables["sharegraph"].Rows)
            {

                Stock stock = new Stock();

                stock.Date = dr["HistoryDate"].ToString();

                stock.Open = Convert.ToDouble(dr["OpenValue"].ToString());

                stock.High = Convert.ToDouble(dr["HighValue"].ToString());

                stock.Low = Convert.ToDouble(dr["LowValue"].ToString());

                stock.Close = Convert.ToDouble(dr["CloseValue"].ToString());

                plotGraphList.Add(stock);

            }
        }

        return plotGraphList;
    }

    [WebMethod]
    public List<Stock> DisplayOwnStock(string UserName)
    {
        List<Stock> plotGraphList = new List<Stock>();
        mySqlConnection = new SqlConnection(GetConnectionString());
        DataSet dataTable = null;

        dataTable = this.LoadStockData(UserName);

        foreach (DataRow dr in dataTable.Tables["data"].Rows)
        {

            Stock stock = new Stock();

            stock.Name = dr["ShareName"].ToString();

            stock.Open = Convert.ToDouble(dr["CloseValue"].ToString());

            plotGraphList.Add(stock);

        }

        return plotGraphList;
    }

    private DataSet LoadStockData(string UserName)
    {
        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "SELECT DISTINCT UserCurrentStock.UserName, UserCurrentStock.ShareName, ShareSimulation.CloseValue FROM UserCurrentStock INNER JOIN Game ON UserCurrentStock.GameID = Game.GameID INNER JOIN ShareSimulation ON UserCurrentStock.GameID = ShareSimulation.GameID AND Game.GameElapsedDate = ShareSimulation.HistoryDate AND UserCurrentStock.ShareName = ShareSimulation.ShareSymbol WHERE (UserCurrentStock.UserName = @UserName)";
            mySqlCommand.CommandType = CommandType.Text;
            mySqlCommand.Parameters.AddWithValue("@UserName", UserName);

            mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            myDataSet = new DataSet();

            OpenConnection();

            mySqlDataAdapter.Fill(myDataSet, "data");

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }

        return myDataSet;
    }

    [WebMethod]
    public List<List<Stock>> DisplayCompareStockHistory(string shareSymbol, string gameID, string startDate, string endDate)
    {
        List<List<Stock>> plotGraphList = new List<List<Stock>>();
        

        int num;
        if (Int32.TryParse(gameID, out num))
        {
            char[] seperator = { ',' };
            string[] values = shareSymbol.Split(seperator);

            mySqlConnection = new SqlConnection(GetConnectionString());
            DataSet dataTable = null;

            for (int i = 0; i < values.Length; i++)
            {
                dataTable = this.LoadData(values[i].ToString(), gameID, startDate, endDate);
                List<Stock> stockList = new List<Stock>();

                foreach (DataRow dr in dataTable.Tables["sharegraph"].Rows)
                {

                    Stock stock = new Stock();

                    stock.Date = dr["HistoryDate"].ToString();

                    stock.Open = Convert.ToDouble(dr["CloseValue"].ToString());

                    //stock.Name = dr["ShareSymbol"].ToString();

                    stockList.Add(stock);

                }

                plotGraphList.Add(stockList);
            }
        }

        return plotGraphList;
    }

    [WebMethod]
    public Game GetGameDateRange(string gameID)
    {

        Game game = new Game();
        int num;
        if (Int32.TryParse(gameID, out num))
        {
            mySqlConnection = new SqlConnection(GetConnectionString());
            DataSet dataTable = null;

            dataTable = this.LoadData(gameID);

            foreach (DataRow dr in dataTable.Tables["dates"].Rows)
            {
                game.StartDate = dr["GameStart"].ToString();

                game.EndDate = dr["GameEnd"].ToString();

            }
        }

        return game;
    }

    private DataSet LoadData(string shareSymbol, string gameID, string startDate, string endDate)
    {
        int num;
        if (Int32.TryParse(gameID, out num))
        {
            try
            {
                mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "GetSharePlotGraph";
                mySqlCommand.CommandType = CommandType.StoredProcedure;
                mySqlCommand.Parameters.AddWithValue("@ShareSymbol", shareSymbol);
                mySqlCommand.Parameters.AddWithValue("@GameID", gameID);
                mySqlCommand.Parameters.AddWithValue("@StartDate", startDate);
                mySqlCommand.Parameters.AddWithValue("@EndDate", endDate);

                mySqlDataAdapter = new SqlDataAdapter();
                mySqlDataAdapter.SelectCommand = mySqlCommand;
                myDataSet = new DataSet();

                OpenConnection();

                mySqlDataAdapter.Fill(myDataSet, "sharegraph");

                CloseConnection();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex2)
            {
                throw ex2;
            }
        }

        return myDataSet;
    }

    private DataSet LoadData(string gameID)
    {
        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "GetGameDateRange";
            mySqlCommand.CommandType = CommandType.StoredProcedure;
            mySqlCommand.Parameters.AddWithValue("@GameID", gameID);

            mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            myDataSet = new DataSet();

            OpenConnection();

            mySqlDataAdapter.Fill(myDataSet, "dates");

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }

        return myDataSet;
    }

    private void CloseConnection()
    {
        try
        {
            if (mySqlConnection.State == ConnectionState.Open)
                mySqlConnection.Close();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }
    }

    private void OpenConnection()
    {
        try
        {
            if (mySqlConnection.State != ConnectionState.Open)
                mySqlConnection.Open();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }
    }

    private string GetConnectionString()
    {
        //return ConfigurationManager.ConnectionStrings["stockmarketConnectionString"].ConnectionString;
        return ConfigurationManager.ConnectionStrings["stockmarketConnectionString"].ConnectionString;
    }

    [WebMethod]
    public List<Transaction> DisplayTransaction(string ShareName, string GameID, String UserName)
    {
        List<Transaction> plotGraphList = new List<Transaction>();
        int num;
        if (Int32.TryParse(GameID, out num))
        {

            mySqlConnection = new SqlConnection(GetConnectionString());
            DataSet dataTable = null;

            /*
            char[] seperator = {','};
            string[] values = ShareName.Split(seperator);
            string share = values[0];
             * */

            dataTable = this.LoadTransactionData(ShareName, GameID, UserName);

            foreach (DataRow dr in dataTable.Tables["transaction"].Rows)
            {

                Transaction tran = new Transaction();

                tran.TransactionID = Convert.ToInt32(dr["TransactionID"].ToString());
                tran.BuyDate = dr["buydate"].ToString();
                tran.BuyQuantity = Convert.ToInt32(dr["buyQuantity"].ToString());
                tran.BuyValue = Convert.ToDouble(dr["buyValue"].ToString());
                tran.BuyTotal = Convert.ToDouble(dr["buyTotal"].ToString());

                tran.SellDate = dr["selldate"].ToString();
                if (tran.SellDate != "")
                {
                    tran.SellQuantity = Convert.ToInt32(dr["sellQuantity"].ToString());
                    tran.SellValue = Convert.ToDouble(dr["sellValue"].ToString());
                    tran.SellTotal = Convert.ToDouble(dr["sellTotal"].ToString());
                    plotGraphList.Add(tran);
                }
                else
                {
                    tran.SellQuantity = 0;
                    tran.SellValue = 0;
                    tran.SellTotal = 0;
                    plotGraphList.Add(tran);
                }
            }
        }

        return plotGraphList;
    }

    private DataSet LoadTransactionData(string ShareName, string GameID, String UserName)
    {
        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "GetTransaction";
            mySqlCommand.CommandType = CommandType.StoredProcedure;
            mySqlCommand.Parameters.AddWithValue("@ShareName", ShareName);
            mySqlCommand.Parameters.AddWithValue("@GameID", GameID);
            mySqlCommand.Parameters.AddWithValue("@UserName", UserName);

            mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            myDataSet = new DataSet();

            OpenConnection();

            mySqlDataAdapter.Fill(myDataSet, "transaction");

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }

        return myDataSet;
    }

    [WebMethod]
    public List<Stock> DisplayCurrentMarketValue(string UserName)
    {
        mySqlConnection = new SqlConnection(GetConnectionString());
        DataSet dataTable = null;

        List<Stock> plotGraphList = new List<Stock>();

        dataTable = this.LoadPieCharData(UserName);

        foreach (DataRow dr in dataTable.Tables["marketvalue"].Rows)
        {

            Stock stock = new Stock();

            stock.Name = dr["ShareName"].ToString();

            stock.Open = Convert.ToDouble(dr["marketvalue"].ToString());

            plotGraphList.Add(stock);

        }

        return plotGraphList;
    }

    private DataSet LoadPieCharData(string userID)
    {
        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "GeTCurrentMarketValue";
            mySqlCommand.CommandType = CommandType.StoredProcedure;
            mySqlCommand.Parameters.AddWithValue("@UserName", userID);

            mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            myDataSet = new DataSet();

            OpenConnection();

            mySqlDataAdapter.Fill(myDataSet, "marketvalue");

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }

        return myDataSet;
    }

    [WebMethod]
    public List<Stock> DisplayStock(string shareSymbol, string gameID, string graph, string duration)
    {
        if (!graph.Equals("undefined") && !duration.Equals("undefined"))
        {
            DataTable dataTable = null;

            if (duration.Equals("Weekly"))
                dataTable = (DataTable)HttpContext.Current.Cache.Get("Weekly");

            else if (duration.Equals("Monthly"))
                dataTable = (DataTable)HttpContext.Current.Cache.Get("Monthly");

            else if (duration.Equals("Yearly"))
                dataTable = (DataTable)HttpContext.Current.Cache.Get("Yearly");

            List<Stock> plotGraphList = new List<Stock>();

            //if (dataTable == null)
            //{
                dataTable = this.LoadGraphData(shareSymbol, gameID, graph, duration);
            //}

            foreach (DataRow dr in dataTable.Rows)
            {

                Stock stock = new Stock();

                stock.Date = dr["HistoryDate"].ToString();

                stock.Open = Convert.ToDouble(dr["OpenValue"].ToString());

                stock.High = Convert.ToDouble(dr["HighValue"].ToString());

                stock.Low = Convert.ToDouble(dr["LowValue"].ToString());

                stock.Close = Convert.ToDouble(dr["CloseValue"].ToString());

                plotGraphList.Add(stock);

            }

            return plotGraphList;
        }

        return null;
    }

    private DataTable LoadGraphData(string shareSymbol, string gameID, string graph, string duration)
    {
        mySqlConnection = new SqlConnection(GetConnectionString());
        mySqlCommand = mySqlConnection.CreateCommand();

        if (duration.Equals("Weekly"))
            mySqlCommand.CommandText = "GetSharePlotGraphWeekly";

        else if (duration.Equals("Monthly"))
            mySqlCommand.CommandText = "GetSharePlotGraphMonthly";

        else if (duration.Equals("Yearly"))
            mySqlCommand.CommandText = "GetSharePlotGraphYearly";

        mySqlCommand.CommandType = CommandType.StoredProcedure;
        mySqlCommand.Parameters.AddWithValue("@ShareSymbol", shareSymbol);
        mySqlCommand.Parameters.AddWithValue("@GameId", gameID);

        mySqlDataAdapter = new SqlDataAdapter();
        mySqlDataAdapter.SelectCommand = mySqlCommand;
        myDataSet = new DataSet();

        OpenConnection();

        mySqlDataAdapter.Fill(myDataSet, "marketvalue");

        CloseConnection();
        return myDataSet.Tables["marketvalue"];
    }

    [WebMethod]
    public string CheckStock(string gameID)
    {        
        mySqlConnection = new SqlConnection(GetConnectionString());
        mySqlCommand = mySqlConnection.CreateCommand();

        mySqlCommand.CommandText = @"	
        SELECT
	    GameElapsedDate 
	    from
	    Game
	    where
	    GameID=@GameID";
        mySqlCommand.CommandType = CommandType.Text;
        mySqlCommand.Parameters.AddWithValue("@GameId", gameID);

        OpenConnection();

        string gameDate = ((DateTime)mySqlCommand.ExecuteScalar()).ToShortDateString();

        CloseConnection();
        
        return gameDate;

    }

    
}
